# -*- coding: utf-8 -*-
tmp_log =""

def readJSON():
	import json
	file=open("make_data.json")
	content=file.read()
	# print content
	data=json.loads(content)
	return data


def getData(cassandra_host,mysql_host,database,username,password,start,end):
	import MySQLdb
	from cassandra.cluster import Cluster
	cluster = Cluster([cassandra_host])
	try:
		session = cluster.connect('ss')
	except Exception, e:
		return " cassandra_host_collect Cassandra is a misconfiguration."
	
	try:
		db = MySQLdb.connect(mysql_host,username,password,database )
	except:
		return "Mysql is misconfigured"
	cursor = db.cursor()
	cursor.execute("delete from item_data;")
	# rows = session.execute("select opc_items from collector;")
	# list_item=[]
	# for (opc_items) in rows:
	# 	for item in opc_items:
	# 		list_item+=item
	# print list_item
	data = readJSON()
	list_item=data['collector']['opc_items']
	print list_item
	all_count=0
	for item_id in list_item:
		 start_time = start
		 while True:

		 	
			 sql = "select item_id,long_time,write_count,project_id,create_time,timestamp,value from data where item_id = '"+item_id+"' and long_time >= "+str(start_time)+" and long_time <= "+str(end)+" order by long_time asc limit 10000"
			 sql_insert = "insert into item_data(item_id,long_time,write_count,project_id,create_time,timestamp,value) values ('test',1,1,'test','test','test','test')"
			 rows = session.execute(sql)
			 count =0
			 for item in rows:
			 	sql_insert+=",('"+str(item.item_id)+"','"+str(item.long_time)+"',"+str(item.write_count)+",'"+item.project_id+"','"+item.create_time+"','"+item.timestamp+"','"+item.value+"')"
		 		# print item_id,long_time,write_count,project_id,create_time,timestamp
		 		count+=1
		 		all_count+=1
		 		# 取最后个时间
		 		start_time = item.long_time
		 	 # print sql_insert
			 cursor.execute(sql_insert)
			 print "insert ",count," records..."
			 db.commit()
			 if count<10000:
			 	break
	return "A total of "+str(all_count)+"  records were requested."

def sendData(cassandra_host,mysql_host,database,username,password):
	import MySQLdb
	from cassandra.cluster import Cluster
	cluster = Cluster([cassandra_host])
	try:
		session = cluster.connect('ss')
	except Exception, e:
		return "cassandra_host_ss Cassandra is a misconfiguration."
	
	try:
		db = MySQLdb.connect(mysql_host,username,password,database )
	except:
		return "Mysql is misconfigured"
	cursor = db.cursor()
	all_count = 0
	i_count=0
	while True: 
	   sql = "select item_id,long_time,write_count,project_id,create_time,timestamp,value from item_data where item_id != 'test' limit "+str(i_count)+",200"
	   i_count+=200
	   count=0
	   cursor.execute(sql)
	   # 获取所有记录列表
	   results = cursor.fetchall()
	   tmp="BEGIN BATCH "
	   for item in results:
	   		item=list(item)
	   		obj ={'item_id':item[0],
	   			'long_time':item[1],
				'write_count':item[2],
				'project_id':item[3],
				'create_time':item[4],
				'timestamp':item[5],
				'value':item[6]}
			try:
				# session.execute("""insert into data (item_id,write_count,project_id,create_time,timestamp,value,long_time) 
				# 	values(%(item_id)s,%(write_count)s,%(project_id)s,%(create_time)s,%(timestamp)s,%(value)s,%(long_time)s)""",obj)
				# session.execute("""insert into data (item_id,write_count,project_id,create_time,timestamp,value,long_time) 
					# values(%(item_id)s,%(write_count)s,%(project_id)s,%(create_time)s,%(timestamp)s,%(value)s,%(long_time)s)""",obj)
				tmp += "insert into data (item_id,write_count,project_id,create_time,timestamp,value,long_time) values\
				('"+obj['item_id']+"',"+str(obj['write_count'])+",'"+obj['project_id']+"','"+obj['create_time']+"','"+obj['timestamp']+"','"+obj['value']+"',"+str(obj['long_time'])+")"
				
				
			except Exception, e:
				raise e
				return "cassandra Insert failed!"
	   		
	   		count+=1
	   		all_count+=1
	   tmp+="APPLY BATCH;"
	   session.execute(tmp)
	   print "sent ",count," records..."
	   if count<200:
	   		break;
	return "A total of "+str(all_count)+" were sent."


if __name__ == '__main__':
	import sys
	import time
	import json
	if len(sys.argv) <4:
		print "getData.py at least 3 argument:action start_time end_time"
		exit()
	action = sys.argv[1]
	if action == 'getData':
		print 'getData'
	elif action == 'sendData':
		print 'sendData'
	else:
		print "First argument action name error!please choose [getData,sendData]"
		exit()


	start_time = sys.argv[2]
	try:
		st=time.strptime(start_time+" 00:00:00","%Y-%m-%d %H:%M:%S")
		st = int(time.mktime(st))
	except Exception, e:
		print "Sendcond argument start_time name error!please set start_time like YYYY-mm-dd."
		exit()

	end_time = sys.argv[3]
	try:
		et=time.strptime(end_time+" 23:59:59","%Y-%m-%d %H:%M:%S")
		et = int(time.mktime(et))
	except Exception, e:
		print "Third argument end_time name error!please set end_time like YYYY-mm-dd."
		exit()

	if et<st:
		print 'The end time cannot be less than the start time.'
		exit()

	#日期不能相差180天
	if et-st>180*60*60*24:
		print "The date difference cannot exceed 180 days."	
		exit()

	file = open("config.json")
	content = file.read()
	config=json.loads(content)
	if action == "getData":
		msg = getData(config['cassandra_host_collect'],
								config['mysql_host'],
								config['database'],
								config['username'],
								config['password'],
								st,
								et)
		print msg
	else:
		msg = sendData(config['cassandra_host_ss'],
                            config['mysql_host'],
                            config['database'],
                            config['username'],
                            config['password'])
		print msg